﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq; 
using Newtonsoft.Json.Linq;
using System.Configuration;
using Dealin.Code;

namespace Dealin.Data.Extensions
{
    /// <summary>
    /// sql操作帮助类
    /// </summary>
    public class DapperDbHelper
    {
        #region 成员
        /// <summary>
        /// 获取或设置数据库连接字符串列表
        /// </summary>
        public string DBConnection
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["DealinDbContext"].ConnectionString;
            }
        }

       

        //数据库连接字符串
        private string dbConnectionString = "";
        /// <summary>
        /// 当前的数据库连接对象
        /// </summary>
        static IDbConnection mConnection = null;
       
        #endregion

        #region 构造 和 基础部分
        /// <summary>
        /// 构造一个sql Db helper 对象
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        public DapperDbHelper(string connString)
        {
            dbConnectionString = connString;
        }
        /// <summary>
        /// 构造一个sql db helper 对象 其中DBConnection从本地xml读取
        /// </summary>
        public DapperDbHelper( )
        {
            dbConnectionString = DBConnection;
        }
        ///// <summary>
        ///// 通过表名，获取控件列表
        ///// </summary>
        ///// <param name="tableName"></param>
        ///// <returns></returns>
        //public List<EasyInputBase> GetControls(string tableName)
        //{
        //    var list = GetFields(tableName);
        //    List<EasyInputBase> ret = new List<EasyInputBase>();
        //    int index = -1;
        //    foreach (var item in list)
        //    {
        //        index++;
        //        switch (item.DbType)
        //        {
        //            case DbType.Binary:
        //            case DbType.Date:
        //            case DbType.DateTime:
        //            case DbType.Int32:
        //            //add.DecimalPlaces = 0;
        //            case DbType.Decimal:
        //            case DbType.Boolean:
        //            case DbType.String:
        //            default:
        //                {

        //                    EasyInputText txt = new EasyInputText();
        //                    txt.DisplayName = item.DisplayName;
        //                    txt.Sql = item.Sql;                            
        //                    txt.Paramters = item.Paramters;
        //                    txt.Key = item.FieldName;
        //                    txt.ValidateSuccss = false;
        //                    txt.Index = index;
        //                    txt.ReadOnly = (bool)item.ReadOnly;


        //                    ret.Add(txt);
        //                }
        //                break;
        //        }
                
        //    }
        //    return ret;
        //}

        /// <summary>
        /// 打开连接 根据列表中的索引打开连接
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public IDbConnection Open()
        {
            if (!string.IsNullOrEmpty(dbConnectionString))
            {
                mConnection = new SqlConnection(dbConnectionString);
                mConnection.Open();
                return mConnection;
            }
            else return null;
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        public void Close()
        {
            if (mConnection != null
                && mConnection.State != System.Data.ConnectionState.Closed)
            {
                mConnection.Close();
            }
        }
        #endregion

        #region CRUD 针对json格式进行CUD操作


        /// <summary>
        /// 查询一个值
        /// </summary>
        /// <param name="sql">sql代码 参数使用@修饰</param>
        /// <param name="par">参数</param>
        /// <returns>返回T</returns>
        public T QueryOne<T>(string sql, object par)
        {
            try
            {
                using (IDbConnection conn = Open())
                {
                    var ret = conn.Query<T>(sql, par);
                    if (ret.Count() <= 0) return default(T);
                    else return ret.FirstOrDefault();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取一个数据集合
        /// </summary>
        /// <param name="sql">sql代码 参数使用@修饰</param>
        /// <param name="par">参数</param>
        /// <returns>返回T的枚举列表</returns>
        public IEnumerable<T> QueryList<T>(string sql, object par)
        {
            try
            {
                using (IDbConnection conn = Open())
                {

                    return conn.Query<T>(sql, par);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 多查询语句的查询
        /// </summary>
        /// <param name="sql">sql代码 参数使用@修饰</param>
        /// <param name="par">参数</param>
        /// <param name="count">查询个数</param>
        /// <param name="ctype">查询语句类型</param>
        /// <returns>多个对象集合</returns>
        public List<dynamic> QueryMultiple(string sql, object par, int count, CommandType ctype)
        {
            try
            {
                List<dynamic> list = new List<dynamic>();
                using (IDbConnection conn = Open())
                {

                    using (SqlMapper.GridReader multi = conn.QueryMultiple(sql, par, null, 600, ctype))
                    {
                        for (int i = 0; i < count; i++)
                            list.Add(multi.Read<dynamic>());
                    }
                    return list;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

       

        /// <summary>
        /// ado 基础查询
        /// </summary>
        /// <param name="sql">sql代码</param>
        /// <returns>返回DataTable</returns>
        public DataTable QueryTable(string sql)
        {
            try
            {
                using (var conn = Open())
                {
                    var da = new System.Data.SqlClient.SqlDataAdapter(sql, conn.ConnectionString);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    if (ds != null && ds.Tables.Count > 0)
                        return ds.Tables[0];
                    else return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 执行存储过程
        /// </summary> 
        /// <param name="proc">存储过程名称</param>
        /// <param name="par">参数列表,支持output参数</param>
        /// <returns>成功的行数</returns>
        public int ExecuteProc(string proc, ref DynamicParameters pars)
        {
            try
            {
                int iret = 0;
                using (IDbConnection conn = Open())
                {
                    iret = conn.Execute(proc, pars, null, 600, CommandType.StoredProcedure);
                }
                return iret;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        public int Execute(string sql, object args)
        {
            try
            {
                int iret = 0;
                using (IDbConnection conn = Open())
                {
                    iret = conn.Execute(sql, args);
                }
                return iret;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            } 
        }
        /// <summary>
        /// 数据库 增删改 操作
        /// </summary>
        /// <param name="tableName">操作的表名</param>
        /// <param name="json">一行数据的json格式</param>
        /// <param name="opType">操作类型</param>
        /// <returns>成功的行数</returns>
        public int ExecuteCUD(string tableName, string json, DbOpType opType)
        {
            try
            {
                var columnSchema = GetSchema(tableName);
                int iret = 0;
                var sql = "";
                switch (opType)
                {
                    case DbOpType.Add:
                        sql = GetInsertForDapper(columnSchema, tableName, json);
                        break;
                    case DbOpType.Edit:
                        sql = GetUpdatForDapper(columnSchema, tableName, json);
                        break;
                    case DbOpType.Delete:
                        sql = GetDeleteForDapper(columnSchema, tableName, json);
                        break;
                    default:
                        iret = 0;
                        break;
                }
                using (IDbConnection conn = Open())
                {
                    iret = conn.Execute(sql, null);
                }
                return iret;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region 公共方法

        #endregion
        /// <summary>
        /// 获取表结构信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public List<ColumnSchema> GetSchema(string tableName)
        {
            try
            {
                if (tableName == null && tableName == "") throw new Exception("未设置数据库表名称!");

                var m_Name = tableName;

                var strSql = "select top 1 OBJECT_ID('" + m_Name + "') as ID";

                var myTableList = QueryOne<int>(strSql, null);

                if (myTableList == 0) throw new Exception("数据库中不存在\"" + tableName + "\"表!");

                #region 很长的脚本
                strSql = @" SELECT  
                            a.id TableID,
                            a.colorder 字段序号,
                            a.name [Name],
                            (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else 0 end) IsIdentity,
                            (case when (SELECT count(*)
                            FROM sysobjects
                            WHERE (name in
                            (SELECT name
                            FROM sysindexes
                            WHERE (id = a.id) AND (indid in
                            (SELECT indid
                            FROM sysindexkeys
                            WHERE (id = a.id) AND (colid in
                            (SELECT colid
                            FROM syscolumns
                            WHERE (id = a.id) AND (name = a.name))))))) AND
                            (xtype = 'PK'))>0 then 1 else 0 end) IsPrimaryKey,
                            case when (select sba.id from sysobjects sba inner join sysforeignkeys fk on sba.id=fk.rkeyid where fk.fkeyid=a.id and fk.fkey=a.colorder )<>0 then 1 else 0 end  as IsForeignKey,
                            (select sba.name from sysobjects sba inner join sysforeignkeys fk on sba.id=fk.rkeyid where fk.fkeyid=a.id and fk.fkey=a.colorder ) as FKTable,
                            (  
                            select pk.name  from sysobjects sba inner join 
                            sysforeignkeys fk on sba.id=fk.rkeyid inner join
                            syscolumns pk on pk.id=sba.id  and pk.colorder=fk.rkey
                            where fk.fkeyid=a.id and fk.fkey=a.colorder ) as FKTableKeyID, 
                            case	when COLUMNPROPERTY(a.id, a.name,'PRECISION')<>-1 and a.xscale=0 and  b.name like '%char%'
                            then b.name+'('+convert(nvarchar(20),COLUMNPROPERTY(a.id, a.name,'PRECISION'))+')' 
                            when COLUMNPROPERTY(a.id, a.name,'PRECISION')=-1   and a.xscale=0 and  b.name like '%char%'
                            then b.name+'('+convert(nvarchar(20),'MAX')+')'
                            when b.name in ('datetime','bit','int') then b.name		
                            else b.name+'('+convert(nvarchar(20),COLUMNPROPERTY(a.id, a.name,'PRECISION'))+','+convert(nvarchar(20),a.xscale)+')' end as FactType, 
                            (case when a.isnullable=1 then 1 else 0 end) 允许空,
                            isnull(e.text,NULL) 默认值,
                            g.[value] AS [Description],   
                            isnull(INDEXPROPERTY(d.id,aa.name, 'IsUnique'),0) AS [IsUnique] 
                            FROM  syscolumns  a left join systypes b 
                            on  a.xtype=b.xusertype
                            inner join sysobjects d 
                            on a.id=d.id  and  d.xtype in('V','U') and  d.name<>'dtproperties'
                            left join syscomments e
                            on a.cdefault=e.id
                            left join sys.extended_properties g
                            on a.id=g.major_id AND a.colid = g.minor_id  
                            left join  sysindexkeys bb ON bb.id = a.id AND bb.colid = a.colid 
                            left join sysindexes aa on aa.id = bb.id AND aa.indid = bb.indid                             
                            where    a.id=" + myTableList + " order by  a.colorder  ";
                #endregion

                var myColumns = QueryList<ColumnSchema>(strSql, null);
                if (myColumns == null || myColumns.Count() <= 0) throw new Exception("数据库中不存在\"" + tableName + "\"表!");

                return myColumns.ToList();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 获取UI需要的字段
        /// </summary>
        /// <param name="args">格式 用冒号分割 args[0] = "字段名:显示名" </param>
        /// <returns>字段列表</returns>
        public List<FieldItem> GetFields(params string[] args)
        {
           

            List<FieldItem> ret = new List<FieldItem>();
            foreach (var item in args)
            {

                var strs = item.Split(':');//分解
                if (strs != null && strs.Length==2) 
                {
                    FieldItem add = new FieldItem();
                    add.DisplayName = strs[1];
                    add.FieldName = strs[0];  
                    add.DbType = DbType.String;
                    add.ReadOnly = true;
                    ret.Add(add);
                }
            }
            return ret;
        }
        /// <summary>
        /// 获取UI需要的字段信息
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <returns>字段列表</returns>
        public List<FieldItem> GetFields(string tableName)
        {
            var list = GetSchema(tableName);
            
            List<FieldItem> ret = new List<FieldItem>();
            foreach (var item in list)
            {
              
                var strs = item.Description.Split(',');//分解
                if (strs != null && strs.Length > 0) //至少有一个描述信息
                {
                    FieldItem add = new FieldItem();
                    add.TableName = tableName;

                    add.FieldName = item.Name;
                    add.DisplayName = strs[0];
                    if (strs.Length > 1)
                        add.ReadOnly = strs[1] == "" ? true : (strs[1]=="1"?true:false);
                    if (strs.Length > 2)
                        add.Sql = strs[2];


                    if (strs.Length > 3)
                    {
                        add.Paramters = new string[strs.Length - 3];
                        for (int i = 3; i < strs.Length; i++)
                        {
                            add.Paramters[i - 3] = strs[i];
                        }
                    }

                    string dbType = item.FactType.ToLower();

                    if (dbType.Contains("image") || dbType.Contains("binary"))
                        add.DbType = DbType.Binary; //图片类型需要转码 因为客户端上传转码为base64 并非是byte类型
                    else if (dbType.Contains("date"))
                        add.DbType = DbType.Date;
                    else if (dbType.Contains("datetime"))
                        add.DbType = DbType.DateTime;
                    else if (dbType.Contains("int"))
                    {
                        add.DbType = DbType.Int32;
                        add.DecimalPlaces = 0;
                    }
                    else if (dbType.Contains("decimal"))
                    {
                        add.DbType = DbType.Decimal;
                        add.DecimalPlaces = 2;
                    }
                    else if (dbType.Contains("float"))
                    {
                        add.DbType = DbType.Decimal;
                        add.DecimalPlaces = 2;
                    }
                    else if (dbType.Contains("double"))
                    {
                        add.DbType = DbType.Decimal;
                        add.DecimalPlaces = 2;
                    }
                    else if (dbType.Contains("bit"))
                        add.DbType = DbType.Boolean;
                    else
                        add.DbType = DbType.String;

                    ret.Add(add);
                }
            }
            return ret;
        }

        /// <summary>
        /// 获取insert语句
        /// </summary>
        /// <param name="sourceTable">数据源表</param>
        /// <returns>insert语句</returns>
        public string GetInsertForDapper(List<ColumnSchema> columns, string tableName, string json)
        { 
            var entity = json.ToObject<dynamic>();
            var jobj = entity as JObject;
            var jsons = jobj.ToList<KeyValuePair<string, JToken>>();
            var keys = from p in jsons
                       select p.Key;

            //组装sql 使得 @字段 赋值


            string fieldstr = "";
            foreach (var item in columns)
            {
                if (!keys.Contains(item.Name.ToString().Trim())) continue;//跳过没有定义的字段 
                if (item.IsIdentity) continue;//跳过标识
                if (fieldstr == "")
                    fieldstr = "[" + item.Name.ToString().Trim() + "]";
                else
                    fieldstr += "," + "[" + item.Name.ToString().Trim() + "]";
            }

            string fieldvalue = "";
            foreach (var item in columns)
            {
                if (!keys.Contains(item.Name.ToString().Trim())) continue;//跳过没有定义的字段 
                if (item.IsIdentity) continue;//跳过标识
                if (fieldvalue == "")
                    fieldvalue = "\'"+json.JsonGetValue(item.Name)+ "\'";
                else
                    fieldvalue += ",\'" + json.JsonGetValue(item.Name) + "\'";
            }

            string restr = @"insert into " + tableName + "( " + fieldstr + " ) values  ( " + fieldvalue + " )";
            return restr;

        }
        /// <summary>
        /// 获取update语句
        /// </summary>
        /// <param name="sourceTable">数据源表</param>
        /// <returns>update语句</returns>
        public string GetUpdatForDapper(List<ColumnSchema> columns, string tableName, string json)
        {

            var entity = json.ToObject<dynamic>();
            var jobj = entity as JObject;
            var jsons = jobj.ToList<KeyValuePair<string, JToken>>();
            var keys = from p in jsons
                       select p.Key;

            string fieldstr = "";
            string wherestr = "";

            foreach (var item in columns)
            {
                if (!keys.Contains(item.Name.ToString().Trim())) continue;//跳过没有定义的字段 
                if (item.IsIdentity) continue;//跳过标识
                if (item.IsPrimaryKey)
                {
                    if (wherestr != "") wherestr += " and ";
                    wherestr += "[" + item.Name.ToString().Trim() + "] = \'" + json.JsonGetValue(item.Name) + "\'"; 
                }
                else
                    if (fieldstr == "")
                    fieldstr = "[" + item.Name.ToString().Trim() + "] = \'" + json.JsonGetValue(item.Name) + "\'";  
                else
                    fieldstr += "," + "[" + item.Name.ToString().Trim() + "] = \'" + json.JsonGetValue(item.Name) + "\'";  
            }

            string restr = @"update  " + tableName + " set " + fieldstr + " where  " + wherestr;
            return restr;

        }
        /// <summary>
        /// 获取delete语句
        /// </summary>
        /// <param name="sourceTable">数据源表</param>
        /// <returns>delete语句</returns>
        public string GetDeleteForDapper(List<ColumnSchema> columns, string tableName, string json)
        { 
            string wherestr = "";

            foreach (var item in columns)
            {
                if (item.IsPrimaryKey)
                {
                    if (wherestr != "") wherestr += " and ";
                    wherestr += "[" + item.Name.ToString().Trim() + "] =  \'" + json.JsonGetValue(item.Name) + "\'";
                }
            } 
            string restr = @"delete  " + tableName + " where  " + wherestr;
            return restr; 
        }

    }
}
